const path = require('path');
const sqlite3 = require('sqlite3').verbose();
const fs = require('fs');
const os = require('os');
const {app} = require('electron');

const dbDir = path.join(os.homedir(), '.gkblocks', 'data'); // 跨平台支持
const dbPath = path.join(dbDir, 'fenbi.db');
// const dbPath = path.join(__dirname, '../fenbi.db');
if (!fs.existsSync(dbDir)) {
    fs.mkdirSync(dbDir, {recursive: true});
}

const db = new sqlite3.Database(dbPath);

// 分页获取考题
function getQuesListWithPagination(limit, offset, type, rating) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT *
                   FROM ques
                   WHERE 1 = 1`;

        if (type && type.trim() !== '') {
            sql += ` AND ques_type = ?`;
            params.push(type.trim());
        }

        if (rating && rating.trim() !== '') {
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseFloat(match[1]);
                const max = parseFloat(match[2]);
                sql += ' AND CAST(REPLACE(rating, "%", "") AS REAL) BETWEEN ? AND ?';
                params.push(min, max);
            }
        }

        sql += ` ORDER BY id LIMIT ? OFFSET ?`;
        params.push(limit, offset);

        db.all(sql, params, (err, rows) => {
                if (err) {
                    console.error('分页查询失败：', err);
                    reject(err);
                } else {
                    resolve(rows);
                }
            }
        );
    });
}

// 获取考题总数
function getQuesCount(type, rating) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT COUNT(*) AS count
                   FROM ques
                   WHERE 1 = 1`;

        if (type && type.trim() !== '') {
            sql += ` AND ques_type = ?`;
            params.push(type.trim());
        }

        if (rating && rating.trim() !== '') {
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseFloat(match[1]);
                const max = parseFloat(match[2]);
                sql += ' AND CAST(REPLACE(rating, "%", "") AS REAL) BETWEEN ? AND ?';
                params.push(min, max);
            }
        }

        db.get(sql, params, (err, row) => {
                if (err) {
                    console.error('获取总数失败：', err);
                    reject(err);
                } else {
                    resolve(row.count);
                }
            }
        );
    });
}

// 按id列表获取考题，结果同列表顺序一致
function getQuesListByIds(ids) {
    return new Promise((resolve, reject) => {
        if (!ids.length) return resolve([]); // 防止空数组报错

        const placeholders = ids.map(() => '?').join(','); // 动态拼接占位符
        const orderCase = ids
            .map((id, index) => `WHEN ? THEN ${index}`)
            .join(' ');
        const sql = `
            SELECT *
            FROM ques
            WHERE id IN (${placeholders})
            ORDER BY CASE id ${orderCase} END
        `;
        const params = [...ids, ...ids]; // 传两遍参，一遍用于 IN，一遍用于 ORDER BY CASE

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

// 获取考题类型及相应的题量
function getQuesTypes(rating) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `
            SELECT ques_type AS type, COUNT(*) AS count
            FROM ques
            WHERE 1 = 1
        `;
        if (rating) {
            // 匹配括号内的数字范围，如 "高（0-40%）" 提取 0 和 40
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseFloat(match[1]);
                const max = parseFloat(match[2]);
                sql += ' AND CAST(REPLACE(rating, "%", "") AS REAL) BETWEEN ? AND ?';
                params.push(min, max);
            }
        }
        sql += ' GROUP BY type ORDER BY count DESC';

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

// 获取考题难度分级及相应题量
function getQuesRatingRanges(type) {
    return new Promise((resolve, reject) => {
        const params = [];
        let whereClause = "WHERE rating IS NOT NULL AND rating != ''";
        if (type && type !== '') {
            whereClause += " AND ques_type = ?";
            params.push(type);
        }
        const sql = `
            SELECT CASE
                       WHEN CAST(REPLACE(rating, '%', '') AS REAL) < 40 THEN '高（0-40%）'
                       WHEN CAST(REPLACE(rating, '%', '') AS REAL) < 70 THEN '中（40-70%）'
                       ELSE '低（70-100%）'
                       END  AS difficulty,
                   COUNT(*) AS count
            FROM ques ${whereClause}
            GROUP BY difficulty
            ORDER BY difficulty
        `;

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows);
            }
        });
    });
}

function getRandomIdList(count, type, rating, exclude) {
    return new Promise((resolve, reject) => {
        const params = [];
        let sql = `SELECT id
                   FROM ques
                   WHERE 1 = 1`;
        if (type) {
            sql += ` AND ques_type = ?`;
            params.push(type);
        }
        if (rating) {
            const match = rating.match(/(\d+)\s*-\s*(\d+)/);
            if (match) {
                const min = parseFloat(match[1]);
                const max = parseFloat(match[2]);
                sql += ' AND CAST(REPLACE(rating, "%", "") AS REAL) BETWEEN ? AND ?';
                params.push(min, max);
            }
        }
        if (exclude && exclude.length) {
            sql += ` AND id NOT IN (${exclude.map(() => '?').join(',')})`;
            params.push(...exclude);
        }
        sql += ` ORDER BY RANDOM() LIMIT ?`;
        params.push(count);

        db.all(sql, params, (err, rows) => {
            if (err) {
                console.error('查询失败：', err);
                reject(err);
            } else {
                resolve(rows.map(row => row.id));
            }
        });
    })
}

module.exports = {
    getQuesListWithPagination,
    getQuesCount,
    getQuesListByIds,
    getQuesTypes,
    getQuesRatingRanges,
    getRandomIdList
};
